package com.api.gbhg.luoao.utils;

import com.api.gbhg.liuwenbo.dao.DataBaseUtil;
import com.api.gbhg.liuwenbo.utils.AlgorithmUtil;
import com.google.common.base.Joiner;
import weaver.conn.RecordSet;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PortalUtils {

    /**
     * 预算执行率
     * @param nd 年度
     * @param sbdw 申报单位
     * @return
     */
    public static RecordSet getTotalImplement(String nd, String sbdw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (sbdw.length()==1 && sbdw.equals("2")){
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd;
        }else{
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jf.sbdw in ("+sbdw+") ";
        }

        rs.executeQuery(sql);
        return rs;
    }


    /**
     * 拨款
     * @param nd 年度
     * @param sbdw 申报单位
     * @param ysly 预算类型
     * @return
     */
    public static RecordSet getAppropriationData(String nd,String sbdw,String ysly){
        RecordSet rs = new RecordSet();
        String sql ;
        if (sbdw.length()==1 && sbdw.equals("2")){
            sql = "select  SUM(je) je,SUM(syed) syed  from uf_jingfeilaiyuan jfly join uf_jingfei_version jv on jfly.requestId = jv.requestId \n" +
                    "where jfly.nd = "+nd+"  and jfly.ysly = "+ysly;
        }else{
            sql = "select  SUM(je) je,SUM(syed) syed  from uf_jingfeilaiyuan jfly join uf_jingfei_version jv on jfly.requestId = jv.requestId \n" +
                    "where jfly.nd = "+nd+"  and jfly.ysly = "+ysly+" and jfly.sbdw in ("+sbdw+") ";
        }

        rs.executeQuery(sql);
        return rs;
    }


    /**
     * 获取项目执行情况统计数据
     * @param pageNum 当前页
     * @param pageSize 当前数据量(条)
     * @param nd 年度
     * @param dw 单位
     * @return
     */
    public static RecordSet getPjStRs(Integer pageNum,Integer pageSize,String nd,String dw){
        RecordSet rs = new RecordSet();
        StringBuilder sb = new StringBuilder();
        if (dw.length()==1 && dw.equals("2")){
            sb.append("select jf.xmmc,jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed,jf.requestId from " +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where jf.nd = "+nd+" group by jf.requestId,jf.xmmc,jf.sbdw  ");
        }else{
            sb.append("select jf.xmmc,jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed,jf.requestId from " +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where jf.nd = "+nd+" and jf.sbdw in ("+dw+") group by jf.requestId,jf.xmmc,jf.sbdw  ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sb.append(" order by requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sb.toString());
        return rs;
    }


    /**
     * 核批任务
     * @param nd 预算年度
     * @return
     */
    public static Map<String,Integer> getApprovalTask(int nd, String dw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.length()==1 && dw.equals("2")){
            sql = "select (select count(id) cut from uf_jingfei_version where  lcztz = 3 and ysnd = "+nd+") processed,count(id) unprocessed \n" +
                    "from  uf_jingfei_version where   lcztz != 3 and ysnd = "+nd+"; ";
        }else{
            sql = "select (select count(id) cut from uf_jingfei_version where  lcztz = 3 and ysnd = "+nd+" and sbdw in ("+dw+")) processed,count(id) unprocessed \n" +
                    "from  uf_jingfei_version where  lcztz != 3 and ysnd = "+nd+" and sbdw in ("+dw+")   ; ";
        }

        rs.executeQuery(sql);
        rs.next();
        Map<String,Integer> map = new HashMap<>();
        map.put("processed",rs.getInt("processed")); //已处理
        map.put("unprocessed",rs.getInt("unprocessed")); //未处理
        return map;
    }


    /**
     * 预算执行
     * @param nd 年度
     * @param dw 单位
     * @return
     */
    public static RecordSet getBudgetImplementation(int nd,String dw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.length()==1 && dw.equals("2")){
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from \n" +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd;
        }else{
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from \n" +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jf.sbdw in ("+dw+")  ";
        }

        rs.executeQuery(sql);
        rs.next();
        return rs;
    }

    /**
     * 项目执行统计总数量
     * @param nd 年度
     * @param dw 单位
     * @return
     */
    public static int getPjStCount(String nd,String dw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.length()==1 && dw.equals("2")){
            sql = "select COUNT(requestId) cut from \n" +
                    "(select jf.requestId from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where  jf.nd = "+nd+"  group by jf.requestId ) tb  ";
        }else{
            sql = "select COUNT(requestId) cut from \n" +
                    "(select jf.requestId from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where  jf.nd = "+nd+"  and jf.sbdw in ("+dw+") group by jf.requestId ) tb  ";
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("cut");
    }


    /**
     * 获取预算核批数据
     * @param pageNum 当前页
     * @param pageSize 页面数量
     * @param nd 年度
     * @param dw 单位
     * @param state 是否完成 true为已完成
     * @return
     */
    public static RecordSet getBudAppData(Integer pageNum,Integer pageSize,int nd,String dw,boolean state){
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (state){
            if(dw.equals("2"))
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and lcztz = 3 ");
            else
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and sbdw in ("+dw+") and lcztz = 3 ");
        }else{
            if(dw.equals("2"))
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+"  and lcztz != 3  ");
            else
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and sbdw in ("+dw+")  and lcztz != 3  ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 获取预算核批数据总条数
     * @param nd
     * @param dw
     * @param state
     * @return
     */
    public static int getBudAppCount(int nd,String dw,boolean state){
        RecordSet rs = new RecordSet();
        String sql ;
        if (state) {
            if (dw.equals("2"))
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and lcztz = 3 ";
            else
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and sbdw in (" + dw + ") and lcztz = 3 ";
        }else{
            if (dw.equals("2"))
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and lcztz != 3 ";
            else
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and sbdw in (" + dw + ") and lcztz != 3 ";
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("cut");
    }

    /**
     * 根据分部id获取所有下级分部
     * @param id 分部id
     * @return
     */
    private static List<Integer> getSubordinate(Integer id){
        RecordSet subComRs = DataBaseUtil.getSubComIdBySupId(id);
        List<Integer> list = new ArrayList<>();
        while (subComRs.next()){
            list.add(subComRs.getInt("id"));
            List<Integer> integers = getSubordinate(subComRs.getInt("id"));
            if(integers.size()>0)
                list.addAll(integers);
        }
        return list;
    }

    /**
     * 根据单位id获取下级单位
     * @param subId  分部id
     * @return
     */
    public static String getSubCompanIdBybmId(Integer subId){
            List<Integer> list = PortalUtils.getSubordinate(subId);
            if (list.isEmpty()){
                list.add(subId);
            }
            return Joiner.on(",").join((Iterable<?>) list);
    }


    /**
     * 下属单位年度执行率情况
     * @param pageNum 当前页
     * @param pageSize 当前数据量(条)
     * @param nd 年度
     * @param dw 单位
     * @return
     */
    public static RecordSet getSubordinate(Integer pageNum,Integer pageSize,String nd,String dw){
        RecordSet rs = new RecordSet();
        StringBuilder sb = new StringBuilder();
        sb.append("select jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed from\n" +
                " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId\n" +
                " where jf.nd = "+nd+" and jf.sbdw in("+dw+") group by jf.sbdw ");

        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sb.append(" order by jf.sbdw offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sb.toString());
        return rs;
    }

    /**
     * 下属单位年度执行率总记录数
     * @param nd 年度
     * @param dw 单位
     * @return
     */
    public static RecordSet getconnt(String nd,String dw){
        RecordSet rs = new RecordSet();
        StringBuilder sb = new StringBuilder();
        sb.append("select jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed from\n" +
                " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId\n" +
                " where jf.nd = "+nd+" and jf.sbdw in("+dw+") group by jf.sbdw ");
        rs.executeQuery(sb.toString());
        return rs;
    }

}
